/**
 * 
 */
package net.sf.cldsql.sql2json.sql;

import java.util.ArrayList;
import java.util.Set;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import net.sf.cldsql.dj.utl.DiException;

/**
 * SqlQuery
 */
public abstract class SqlBaseQuery {

    final static Logger log = LoggerFactory.getLogger(SqlRestQuery.class);
    
    protected short stmtType = 0;

    public static final short SELECT = 1;
    public static final short UPDATE = 2;
    public static final short INSERT = 3;
    public static final short DELETE = 4;
    protected String tableName;
    protected ArrayList<AndEntry> selectList = new ArrayList<AndEntry>();
    protected ArrayList<AndEntry> updateList = new ArrayList<AndEntry>();
    protected ArrayList<AndEntry> whereList = new ArrayList<AndEntry>();
    protected ArrayList<AndEntry> groupByList = new ArrayList<AndEntry>();
    protected ArrayList<AndEntry> orderByList = new ArrayList<AndEntry>();
    protected ArrayList<String> params = new ArrayList<String>();
    protected Long limit = 1000L;

    /**  */
    public SqlBaseQuery() {
        super();
    }

    public StringBuilder buildSql() {
        StringBuilder sql = new StringBuilder();
        
        switch (stmtType) {
        case SELECT:
            buildSelect(sql, params);
            sql.append(" from " + tableName);
            buildWhere(sql, params);
            buildGroupBy(sql);
            buildOrderBy(sql);
            break;
        case UPDATE:
            sql.append("update " + tableName);
            buildUpdateSet(sql, params);
            buildWhere(sql, params);
            break;
        case INSERT:
            sql.append("insert into " + tableName);
            buildInto(sql, params);
            buildValues(sql, params);
            break;
        case DELETE:
            sql.append("delete from " + tableName);
            buildWhere(sql, params);
            break;
        default:
            throw new DiException("Unknown sql statement type " + stmtType);
        }
        
        log.info("sql={} ;| {}", sql, params);
        return sql;
    }

    /** @param sql */
    private void buildGroupBy(StringBuilder sql) {
        if (!groupByList.isEmpty()) {
            sql.append(" group by ");
            boolean needComma = false;
            for (AndEntry entry : groupByList) {
                if (needComma) {
                    sql.append(" ,");
                }
                sql.append(entry.getColumn());
                needComma |= true;
            }
        }
        
    }

    /** @param sql */
    private void buildOrderBy(StringBuilder sql) {
        if (!orderByList.isEmpty()) {
            sql.append(" order by ");
            boolean needComma = false;
            for (AndEntry entry : orderByList) {
                if (needComma) {
                    sql.append(" ,");
                }
                sql.append(entry.getColumn() + " " + entry.getValue());
                needComma |= true;
            }
        }
        
    }

    /**
     * @param sql
     * @param sqlPrm
     */
    protected void buildSelect(StringBuilder sql, ArrayList<String> sqlPrm) {
        if (!selectList.isEmpty()) {
            sql.append("select ");
            
            boolean needComma = false;
            for (AndEntry entry : selectList) {
                
                String op = entry.getOp();
                if (op.equals("") || op.equals("as")) {
                    if (needComma) {
                        sql.append(" ,");
                    }
                    sql.append(( op.equals("as") ? entry.getValue() + " as " : "") + entry.getColumn());
                    needComma |= true;
                } else {
                    throw new DiException("Unknown Operator " + op);
                }
            }
        } else {
            sql.append("select *");
        }
        
    }

    /**
     * @param sql
     * @param sqlPrm
     */
    protected void buildValues(StringBuilder sql, ArrayList<String> sqlPrm) {
        if (!updateList.isEmpty()) {
            sql.append(" values(");
            
            boolean needComma = false;
            for (AndEntry entry : updateList) {
                
                String op = entry.getOp();
                if (op.equals("=")) {
                    if (needComma) {
                        sql.append(", ");
                    }
                    sql.append("?");
                    sqlPrm.add(entry.getValue());
                    needComma |= true;
                } else {
                    throw new DiException("Unknown Operator " + op);
                }
            }
            sql.append(")");
        }
        
    }

    /**
     * @param sql
     * @param sqlPrm
     */
    protected void buildInto(StringBuilder sql, ArrayList<String> sqlPrm) {
        if (!updateList.isEmpty()) {
            sql.append(" (");
            
            boolean needComma = false;
            for (AndEntry entry : updateList) {
                
                String op = entry.getOp();
                if (op.equals("=")) {
                    if (needComma) {
                        sql.append(", ");
                    }
                    sql.append(entry.getColumn());
                    needComma |= true;
                } else {
                    throw new DiException("Unknown Operator " + op);
                }
            }
            
            sql.append(")");
        }
    }

    /**
     * @param sql
     * @param sqlPrm2
     */
    protected void buildUpdateSet(StringBuilder sql, ArrayList<String> sqlPrm) {
        if (!updateList.isEmpty()) {
            sql.append(" set");
            
            boolean needComma = false;
            for (AndEntry entry : updateList) {
                
                String op = entry.getOp();
                if (op.equals("=")) {
                    if (needComma) {
                        sql.append(",");
                    }
                    sql.append(" " + entry.getColumn() + " = ?");
                    sqlPrm.add(entry.getValue());
                    needComma |= true;
                } else {
                    throw new DiException("Unknown Operator " + op);
                }
            }
        }
        
    }

    /**
     * @return
     */
    protected void buildWhere(StringBuilder sql, ArrayList<String> sqlPrm) {
        
        if (!whereList.isEmpty()) {
            sql.append(" where ");
            
            boolean needAnd = false;
            for (AndEntry entry : whereList) {
                
                String op = entry.getOp();
                if (op.equals("(") 
                        || op.equals(")")
                        || op.equals("or")
                        || op.equals("and")
                        ) {
                    if (!op.equals(")")) {
                        needAnd = false;
                    }
                    sql.append(" " + op + " ");
                } else if (op.equals("like")
                        ||op.equals("in")
                        ||op.equals(">")
                        ||op.equals(">=")
                        ||op.equals("<")
                        ||op.equals("<=")
                        ||op.equals("!=")
                        ||op.equals("=")) {
                    if (needAnd) {
                        sql.append(" and ");
                    }
                    if (op.equals("in")) {
                        sql.append(entry.getColumn() + " " + op + " (");
                        String[] inList = entry.getValue().split(",");
                        for (int inIndex = 0; inIndex < inList.length; inIndex ++) {
                            String inValue = inList[inIndex];
                            if (inIndex != 0) {
                                sql.append(",");
                            }
                            sql.append("?");
                            sqlPrm.add(inValue);
                        }
                        sql.append(")");
                    } else if(op.equals("like")) {
                        sql.append(entry.getColumn() + " " + op + " ?");
                        sqlPrm.add(entry.getValue().replace("*", "%"));
                    } else {
                        sql.append(entry.getColumn() + " " + op + " ?");
                        sqlPrm.add(entry.getValue());
                    }
                } else {
                    throw new DiException("Unknown Operator " + op);
                }
            }
        }
    }

    /**
     * @return tableName
     */
    public String getTableName() {
        return tableName;
    }

    /**
     * @return whereList
     */
    public ArrayList<AndEntry> getWhereList() {
        return whereList;
    }

    /**
     * @return parameters
     */
    public ArrayList<String> getParams() {
        return params;
    }

    /**
     * @param parameters 設定する parameters
     */
    public void setParams(ArrayList<String> parameters) {
        this.params = parameters;
    }

}
